﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace Secair_TCP_Server_GB
{
    class SqlServicer
    {
        #region Properties
        SQLiteConnection dbCnn;

        bool _bCnt;//数据库是否连接上，true表示连接上

        string _path;

        #endregion Properties

        public const string c_no_connect_exp = "Database hasn't been connected!";

        #region Constructors and Destructors
        /// <summary>
        /// 初始化一个数据库连接
        /// </summary>
        public SqlServicer(string path)
        {
            _path = path;
            _bCnt = false;
        }

        ~SqlServicer( )
        {
            this.Close();
        }

        #endregion Constructors and Destructors

        #region 连接、关闭数据库
        public bool Connect()
        {
            if (IsConnected == true)
            {
                return true;
            }
            //string dbpath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "BeidouSysSQLite.db";
            //string connectString = "Data Source=" + dbpath + ";Pooling=true;FailIfMissing=false"; /*D:\sqlite.db就是sqlite数据库所在的目录,它的名字你可以随便改的*/

            string strConn = "";
            strConn = "Data Source=" + _path + ";Pooling=true;FailIfMissing=false";
            try
            {
                //SQLiteConnection conn = new SQLiteConnection(connectString); //新建一个连接

                this.dbCnn = new SQLiteConnection(strConn);
                this.dbCnn.Open();
                _bCnt = true;
            }
            catch (Exception /* e */)
            {
                //e.Message;
                this.dbCnn = null;
                _bCnt = false;
                return false;
            }
            return true;
        }

        public void Close()
        {
            if (_bCnt == true)
            {
                try
                {
                    dbCnn.Close();
                    _bCnt = false;
                }
                catch (Exception e)
                { }
            }
        }

        public bool IsConnected
        {
            get { return _bCnt; }
        }

        #endregion 连接、关闭数据库

        #region 数据库基本操作

        //DataAdapter 获取 DataSet
        //QueryResult有返回结果的查询
        public DataSet QueryResult(string strSQL)
        {
            if (IsConnected == false)
            {
                //return null;
                throw new Exception(c_no_connect_exp);
            }

            DataSet dbDataSet = new DataSet();

            ////在适当的时候可以打开，但是这里有助于调试
            //try
            //{
                IDbCommand dbCmd = new SQLiteCommand(strSQL, (SQLiteConnection)this.dbCnn);
                IDbDataAdapter dbDataAdapter = new SQLiteDataAdapter((SQLiteCommand)dbCmd);
                dbDataAdapter.Fill(dbDataSet);
            //}
            //catch (System.Exception /*e*/)
            //{
                
            //}

            return dbDataSet;
        }
        public DataRowCollection Query(string sql)
        {
            DataTable table = new DataTable();

            table.Rows.Clear();

            DataSet dataSet = QueryResult(sql);
            //Never execute
            //if (dataSet == null)
            //{
            //    return table.Rows;
            //}

            if (dataSet.Tables.Count == 0)
            {
                return table.Rows;
            }

            return dataSet.Tables[0].Rows;
        }

        ///<summary>
        ///类型: public
        ///描述: 无返回结果的数据库查询，比如插入，删除，更新
        ///</summary>
        ///<param name="strSQL">string</param>
        ///<return>bool</return>
        ///
        public int QueryNonResult(string strSQL)
        {
            if (IsConnected == false)
            {
                throw new Exception(c_no_connect_exp);
            }

            IDbCommand dbCmd = new SQLiteCommand(strSQL, (SQLiteConnection)this.dbCnn);
            int lineCnt = dbCmd.ExecuteNonQuery();

            return lineCnt;
        }

        public object QueryOneObject(string strSQL)
        {
            if (IsConnected == false)
            {
                throw new Exception(c_no_connect_exp);
            }

            IDbCommand dbCmd = new SQLiteCommand(strSQL, (SQLiteConnection)this.dbCnn);
            object obj = dbCmd.ExecuteScalar();

            return obj;
        }
        #endregion 数据库基本操作

    }
}
